//	Ordinaire.Database.OleDatabase
//	Copyright (c) 2008 Jonathan Loe
//
//	MIT license (http://en.wikipedia.org/wiki/MIT_License)
//
//	Permission is hereby granted, free of charge, to any person obtaining a copy
//	of this software and associated documentation files (the "Software"), to deal
//	in the Software without restriction, including without limitation the rights 
//	to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies
//	of the Software, and to permit persons to whom the Software is furnished to do so, 
//	subject to the following conditions:
//
//	The above copyright notice and this permission notice shall be included in all 
//	copies or substantial portions of the Software.
//
//	THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, 
//	INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR
//	PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE 
//	FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE,
//	ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.

using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Reflection;
using System.Text;

namespace Ordinaire.Db
{
	/// <summary>
	/// OleDatabase is a defined class of Ordinaire.Database.IDatabase interface
	/// that handles System.Data.OleDb connection.
	/// </summary>
	/// <seealso cref="Ordinaire.Database.IDatabase"/>
	public class OleDb : IDatabase
	{
		#region Variables declaration
		private string _connectionString = null;

		private OleDbConnection _connection = null;
		private OleDbTransaction transaction = null;
		#endregion

		#region Constructor
		/// <summary>
		/// Creates new instance of OleDatabase class.
		/// </summary>
		/// <param name="connectionString">connection string</param>
		/// <exception cref="System.ArgumentNullException">thrown when connection string is null or empty</exception>
		public OleDb(string connectionString)
		{
			if (String.IsNullOrEmpty(connectionString))
				throw new ArgumentNullException("connectionString");

			// Test connection
			using (_connection = new OleDbConnection(connectionString))
			{
				_connection.Open();
				_connectionString = connectionString;
			}
		}
		#endregion

		#region Property methods
		public string ConnectionString
		{
			get { return _connectionString; }
		}
		#endregion

		#region Public methods
		#region IDatabase methods
		#region Update methods
		public int Update(object value, string[] conditionFields)
		{
			return Update(value, conditionFields, null, null);
		}

		public int Update(object value, string[] conditionFields, string[] excludeFields)
		{
			return Update(value, conditionFields, excludeFields, null);
		}

		public int Update(object value, string[] conditionFields, string[] excludeFields, string tableName)
		{
			string statement = SqlQuery.GenerateUpdateStatement(value, conditionFields, excludeFields, tableName);
			return ExecuteNonQuery(statement);
		}
		#endregion

		#region Insert methods
		public int Insert(object value)
		{
			return Insert(value, null, null);
		}

		public int Insert(object value, string[] excludeFields)
		{
			return Insert(value, excludeFields, null);
		}

		public int Insert(object value, string[] excludeFields, string tableName)
		{
			string statement = SqlQuery.GenerateInsertStatement(value, excludeFields, tableName);
			return ExecuteNonQuery(statement);
		}
		#endregion

		#region Delete methods
		public int Delete(object value, string[] conditionFields)
		{
			return Delete(value, conditionFields, null);
		}

		public int Delete(object value, string[] conditionFields, string tableName)
		{
			string statement = SqlQuery.GenerateDeleteStatement(value, conditionFields, tableName);
			return ExecuteNonQuery(statement);
		}
		#endregion

		#region ExecuteQuery methods
		public DataTable ExecuteQuery(string commandText)
		{
			return ExecuteQuery(commandText, new Dictionary<string, object>(), CommandType.Text);
		}

		public DataTable ExecuteQuery(string commandText, CommandType commandType)
		{
			return ExecuteQuery(commandText, new Dictionary<string, object>(), commandType);
		}

		public DataTable ExecuteQuery(string commandText, Dictionary<string, object> parameters)
		{
			return ExecuteQuery(commandText, parameters, CommandType.StoredProcedure);
		}

		public DataTable ExecuteQuery(string commandText, Dictionary<string, object> parameters, CommandType commandType)
		{
			if (String.IsNullOrEmpty(commandText)) { throw new ArgumentNullException("commandText"); }

			DataSet dataSet = new DataSet();
			OleDbConnection connection = GetConnection();
			try
			{
				using (OleDbCommand command = new OleDbCommand(commandText, connection))
				{
					SetCommand(command, commandType, parameters);

					// Open connection
					if (connection.State != ConnectionState.Open) { connection.Open(); }

					using (OleDbDataAdapter adapter = new OleDbDataAdapter(command))
					{
						adapter.Fill(dataSet);
						return dataSet.Tables[0].Copy();
					}
				}
			}
			finally { CloseConnection(connection); }
		}

		public List<object> ExecuteQuery(TransformRowToObject transformer, string commandText)
		{
			return ExecuteQuery(transformer, commandText, new Dictionary<string, object>(), CommandType.Text);
		}

		public List<object> ExecuteQuery(TransformRowToObject transformer, string commandText, CommandType commandType)
		{
			return ExecuteQuery(transformer, commandText, new Dictionary<string, object>(), commandType);
		}

		public List<object> ExecuteQuery(TransformRowToObject transformer, string commandText, Dictionary<string, object> parameters)
		{
			return ExecuteQuery(transformer, commandText, parameters, CommandType.StoredProcedure);
		}

		public List<object> ExecuteQuery(TransformRowToObject transformer, string commandText, Dictionary<string, object> parameters, CommandType commandType)
		{
			if (String.IsNullOrEmpty(commandText)) { throw new ArgumentNullException("commandText"); }

			List<object> resultList = null;
			OleDbConnection connection = GetConnection();
			try
			{
				using (OleDbCommand command = new OleDbCommand(commandText, connection))
				{
					SetCommand(command, commandType, parameters);

					// Open connection
					if (connection.State != ConnectionState.Open) { connection.Open(); }

					using (OleDbDataReader reader = command.ExecuteReader())
					{
						resultList = new List<object>();
						while (reader.Read())
						{
							Dictionary<string, object> row = ConvertToDictionary(reader);
							resultList.Add(transformer(row));
						}
					}
				}
			}
			finally { CloseConnection(connection); }

			return resultList;
		}
		#endregion

		#region ExecuteScalar methods
		public object ExecuteScalar(string commandText)
		{
			return ExecuteScalar(commandText, new Dictionary<string, object>(), CommandType.Text);
		}

		public object ExecuteScalar(string commandText, CommandType commandType)
		{
			return ExecuteScalar(commandText, new Dictionary<string, object>(), commandType);
		}

		public object ExecuteScalar(string commandText, Dictionary<string, object> parameters)
		{
			return ExecuteScalar(commandText, parameters, CommandType.StoredProcedure);
		}

		public object ExecuteScalar(string commandText, Dictionary<string, object> parameters, CommandType commandType)
		{
			if (String.IsNullOrEmpty(commandText)) { throw new ArgumentNullException("commandText"); }

			OleDbConnection connection = GetConnection();
			try
			{
				using (OleDbCommand command = new OleDbCommand(commandText, connection))
				{
					SetCommand(command, commandType, parameters);

					// Open connection
					if (connection.State != ConnectionState.Open) { connection.Open(); }
					return command.ExecuteScalar();
				}
			}
			finally { CloseConnection(connection); }
		}
		#endregion

		#region ExecuteNonQuery
		public int ExecuteNonQuery(string commandText)
		{
			return ExecuteNonQuery(commandText, new Dictionary<string, object>(), CommandType.Text);
		}

		public int ExecuteNonQuery(string commandText, CommandType commandType)
		{
			return ExecuteNonQuery(commandText, new Dictionary<string, object>(), commandType);
		}

		public int ExecuteNonQuery(string commandText, Dictionary<string, object> parameters)
		{
			return ExecuteNonQuery(commandText, parameters, CommandType.StoredProcedure);
		}

		public int ExecuteNonQuery(string commandText, Dictionary<string, object> parameters, CommandType commandType)
		{
			if (String.IsNullOrEmpty(commandText)) { throw new ArgumentNullException("commandText"); }

			OleDbConnection connection = GetConnection();
			try
			{
				using (OleDbCommand command = new OleDbCommand(commandText, connection))
				{
					SetCommand(command, commandType, parameters);

					// Open connection
					if (connection.State != ConnectionState.Open) { connection.Open(); }
					return command.ExecuteNonQuery();
				}
			}
			finally { CloseConnection(connection); }
		}
		#endregion

		#region Transaction related...
		public bool StartTransaction()
		{
			if (transaction != null)
				throw new InvalidOperationException(ExceptionMessage.DatabaseTransactionStarted);

			_connection = new OleDbConnection(_connectionString);
			transaction = _connection.BeginTransaction();

			return (transaction != null);
		}

		public void Commit()
		{
			if (transaction == null)
				throw new InvalidOperationException(ExceptionMessage.DatabaseTransactionNotStarted);

			transaction.Commit();
			_connection.Close();
		}

		public void Rollback()
		{
			if (transaction == null)
				throw new InvalidOperationException(ExceptionMessage.DatabaseTransactionNotStarted);

			transaction.Rollback();
			_connection.Close();
		}
		#endregion
		#endregion
		#endregion

		#region Private methods
		/// <summary>
		/// Creates new database connection or use the existing if a transaction has been initiated.
		/// </summary>
		/// <returns>database connection</returns>
		private OleDbConnection GetConnection()
		{
			if (transaction != null) { return _connection; }
			return new OleDbConnection(_connectionString);
		}

		/// <summary>
		/// Closes open connection.
		/// </summary>
		private void CloseConnection(OleDbConnection connection)
		{
			// Don't close if there's transaction
			if (transaction != null && transaction.Connection != null) { return; }

			// Close connection
			if (connection.State == ConnectionState.Open) { connection.Close(); }
		}

		/// <summary>
		/// Set command accordingly.
		/// </summary>
		/// <param name="command">command</param>
		/// <param name="commandType">command type</param>
		/// <param name="parameters">list of parameters</param>
		private void SetCommand
		(
			OleDbCommand command,
			CommandType commandType,
			Dictionary<string, object> parameters
		)
		{
			if (command == null) { throw new ArgumentException("command"); }

			command.CommandType = commandType;
			command.Transaction = transaction;

			if (parameters != null && parameters.Count > 0)
			{
				foreach (KeyValuePair<string, object> parameter in parameters)
				{
					OleDbParameter oleParameter = new OleDbParameter(parameter.Key, parameter.Value);
					command.Parameters.Add(oleParameter);
				}
			}
		}

		/// <summary>
		/// Converts a single row to a pair of type string and object.
		/// </summary>
		/// <param name="reader">data reader</param>
		/// <returns>dictionary instance of string and object</returns>
		private Dictionary<string, object> ConvertToDictionary(OleDbDataReader reader)
		{
			Dictionary<string, object> dictionary = new Dictionary<string, object>();
			for (int i = 0; i < reader.FieldCount; i++) { dictionary.Add(reader.GetName(i), reader.GetValue(i)); }
			return dictionary;
		}
		#endregion
	}
}
